package com.zqy.common.util;

import org.springframework.stereotype.Component;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.*;

/**
 * 基础操作Dao
 *
 * @author yang
 * 
 */
@Component
public class BaseDao {


	


//	// 获取条件查询的所有的记录
//	public List<?> getAll(final String keyword) {
//		return hibernateTemplate.executeFind(new HibernateCallback() {
//			public Object doInHibernate(Session session) throws HibernateException, SQLException {
//				Query query = session.createQuery(keyword);
//				return query.list();
//			}
//		});
//	}	
	

	
	
	// 获取所有的记录 加排序和分页

	

	

    /**
     * 通过JDBC执行SQL增删改
     */
    public void excuteSqlByJDBC(String url,String username,String password,String keyword)
    {
    	try {
			Class.forName("com.kingbase8.Driver");
    		Class.forName("com.mysql.jdbc.Driver");
//			Class.forName("oracle.jdbc.driver.OracleDriver");
//			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    	} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}// 加载Oracle驱动程序
    	String myurl=url;
    	String myusername=username;
    	String mypassword=password;
    	String mykeyword=keyword;
    	if(url==null||username==null||password==null)
    	{
    		myurl = getPropertiesFile("jdbc.properties").getProperty("jdbc.url");
    		myusername=getPropertiesFile("jdbc.properties").getProperty("jdbc.username");
    		mypassword=getPropertiesFile("jdbc.properties").getProperty("jdbc.password");
    	}
    	 Connection conn=null;
    	 Statement stmt=null;

    	try {
		 conn=DriverManager.getConnection(myurl,myusername,mypassword);
         stmt = conn.createStatement();
         stmt.execute(keyword);
         }catch (SQLException e) {
			e.printStackTrace();

		}finally
		{
			try {

			stmt.close();
			conn.close();
		}catch (SQLException e) {
			e.printStackTrace();

		}
		}
    }
    /**
     * 使用JDBC链接数据库,查询结果集
     *  @param url 链接地址
     *  @param username 用户名
     *  @param password 密码
     *  @param keyword sql语句 
     */
    public List getDateByJDBC(String url,String username,String password,String keyword)
    {
    	
    	try {
			Class.forName("com.kingbase8.Driver");
    		Class.forName("com.mysql.jdbc.Driver");
//			Class.forName("oracle.jdbc.driver.OracleDriver");
//			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}// 加载Oracle驱动程序
    	String myurl=url;
    	String myusername=username;
    	String mypassword=password;
    	String mykeyword=keyword;
    	if(url==null||username==null||password==null)
    	{
    		myurl = getPropertiesFile("jdbc.properties").getProperty("jdbc.url");
    		myusername=getPropertiesFile("jdbc.properties").getProperty("jdbc.username");
    		mypassword=getPropertiesFile("jdbc.properties").getProperty("jdbc.password");
    	}
    	 Connection conn=null;
    	 Statement stmt=null;
    	 ResultSet rs=null;
    	try {
		 conn=DriverManager.getConnection(myurl,myusername,mypassword);
         stmt = conn.createStatement();
		//PreparedStatement pstmt = conn.prepareStatement(mykeyword) ;
         rs=stmt.executeQuery(mykeyword);
         List list = new ArrayList();

         ResultSetMetaData md = rs.getMetaData();

         int columnCount = md.getColumnCount();

         while (rs.next()) {

             Map rowData = new HashMap();

             for (int i = 1; i <= columnCount; i++) {
            	 Object obj=rs.getObject(i);
            	 if(obj instanceof  java.sql.Date)
            	 {
            		 obj=new java.util.Date(((java.sql.Date) obj).getTime());
            		 obj=obj.toString();
            	 }
                 rowData.put(md.getColumnName(i), obj);

             }

             list.add(rowData);

         }

         return list;

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally
		{
			try {
			rs.close();
			stmt.close();
			conn.close();
		}catch (SQLException e) {
			e.printStackTrace();

		}

		}
    	
    	
    }
    
    /**
     * 读取Properties文件
     */
    public Properties getPropertiesFile(String filename)
    {
    	Properties props;
		
		props = new Properties();
    	try {
			props.load(ReadJDBC.class.getClassLoader().getResourceAsStream(
					filename));
			return props;
		} catch (IOException e) {
			e.printStackTrace();
			return null;
			
		}		
    }
    
    /**
     * 设定Properties值
     * @param
     * @param key           
     * @param value
     */
    public void setProperties(String resourceFile,String key,String value)
    {
      
    	   Properties prop = new Properties();        
           try {
               if(resourceFile.indexOf(".properties")==-1){
                   resourceFile+=".properties";
               }
               FileInputStream fis = new FileInputStream(resourceFile);
               try {
                   prop.load(fis);
                   fis.close();
                   prop.setProperty(key, value);
                   FileOutputStream fos = new FileOutputStream(resourceFile);
                   prop.store(fos, "Copyright Thcic");
                   fos.close();
               } catch (IOException e) {
                   e.printStackTrace();
                   System.out.println("修改资源文件："+resourceFile+"异常！msg："+e.getMessage());
               }
                
           } catch (FileNotFoundException e) {
               e.printStackTrace();
               System.out.println("无法获得资源文件：" + resourceFile);
           }    	
    }
    
    /**
     * 防注入,替换关键字中的符号
     * @param str
     * @return
     */
    public static String TransactSQLInjection(String str)
    {
    	// 我认为 应该是  return str.replaceAll(".*([';]+|(--)+).*", " ");

       return str.replaceAll("([';])+|(--)+","");

    }
}
